using Dapper;
using MediatR;
using Microsoft.Extensions.Logging;
using RBAC.Domain.Dto.Output;
using RBAC.Domain.PurchasingManagement.PurchaseOrders;
using RBAC.ErrorCode;
using RBAC.Interstructrue.Base;
using RBAC.Read.Api.Application.Command.ERP.PurchasingManagement.PurchaseOrders;
using RBAC.Read.Api.Application.Dto.ERP;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace RBAC.Read.Api.Application.Handler.ERP.PurchasingManagement.PurchaseOrders
{
    /// <summary>
    /// 采购订单与采购物料联查（分页）
    /// </summary>
    public class GetPurchaseOrdersWithMaterialsHandler : IRequestHandler<GetPurchaseOrdersWithMaterialsCommand, APIPaging<PurchaseOrderWithMaterialDto>>
    {
        private readonly IBaseRepository<PurchaseOrderInfo> _repo;
        private readonly ILogger<GetPurchaseOrdersWithMaterialsHandler> _logger;

        public GetPurchaseOrdersWithMaterialsHandler(
            IBaseRepository<PurchaseOrderInfo> repo,
            ILogger<GetPurchaseOrdersWithMaterialsHandler> logger)
        {
            _repo = repo;
            _logger = logger;
        }

        public Task<APIPaging<PurchaseOrderWithMaterialDto>> Handle(GetPurchaseOrdersWithMaterialsCommand request, CancellationToken cancellationToken)
        {
            var res = new APIPaging<PurchaseOrderWithMaterialDto>();
            try
            {
                // FROM + 基础过滤
                var fromSql = @" FROM PurchaseOrderInfo p
                                  INNER JOIN PurchasingMaterialInfo m ON m.PurchaseOrderId = p.Id AND m.IsDel = 0
                                  LEFT JOIN Supplier s ON s.Id = CAST(p.SupplierId AS SIGNED)
                                  WHERE p.IsDeleted = 0";

                var whereParts = new List<string>();
                var param = new DynamicParameters();

                if (!string.IsNullOrWhiteSpace(request.DocCode))
                {
                    whereParts.Add("p.DocCode LIKE @DocCode");
                    param.Add("DocCode", $"%{request.DocCode}%");
                }
                if (!string.IsNullOrWhiteSpace(request.DocumentaryNumber))
                {
                    whereParts.Add("m.DocumentaryNumber LIKE @DocumentaryNumber");
                    param.Add("DocumentaryNumber", $"%{request.DocumentaryNumber}%");
                }
                if (!string.IsNullOrWhiteSpace(request.PlannedOrderNumber))
                {
                    whereParts.Add("m.PlannedOrderNumber LIKE @PlannedOrderNumber");
                    param.Add("PlannedOrderNumber", $"%{request.PlannedOrderNumber}%");
                }
                if (!string.IsNullOrWhiteSpace(request.MaterialCode))
                {
                    whereParts.Add("m.materialCode LIKE @MaterialCode");
                    param.Add("MaterialCode", $"%{request.MaterialCode}%");
                }
                if (!string.IsNullOrWhiteSpace(request.MaterialName))
                {
                    whereParts.Add("m.materialName LIKE @MaterialName");
                    param.Add("MaterialName", $"%{request.MaterialName}%");
                }
                if (!string.IsNullOrWhiteSpace(request.Supplier))
                {
                    // 供应商名称模糊匹配；兼容 p.supplierId 可能存放名称/别名/简称/非数值ID 的情况
                    whereParts.Add(@"(
                        s.Supplier_Name LIKE @Supplier OR s.Supplier_Alias LIKE @Supplier OR s.Short_Name LIKE @Supplier
                        OR p.supplierId LIKE @Supplier
                        OR EXISTS (
                              SELECT 1 FROM Supplier s2
                              WHERE (s2.Supplier_Name LIKE @Supplier OR s2.Supplier_Alias LIKE @Supplier OR s2.Short_Name LIKE @Supplier)
                                AND (CAST(p.supplierId AS SIGNED) = s2.Id OR p.supplierId = s2.Supplier_Name)
                        )
                    )");
                    param.Add("Supplier", $"%{request.Supplier}%");
                }
                if (request.DocDateStart.HasValue)
                {
                    whereParts.Add("p.docDate >= @DocDateStart");
                    param.Add("DocDateStart", request.DocDateStart.Value.Date);
                }
                if (request.DocDateEnd.HasValue)
                {
                    whereParts.Add("p.docDate < @DocDateEnd");
                    param.Add("DocDateEnd", request.DocDateEnd.Value.Date.AddDays(1));
                }

                var whereSql = whereParts.Count > 0 ? (" AND " + string.Join(" AND ", whereParts)) : string.Empty;

                // 1) 统计联查总条数（以明细行为单位）
                var countSql = "SELECT COUNT(1)" + fromSql + whereSql;
                var total = _repo.Query<int>(countSql, param).FirstOrDefault();

                res.TotalCount = total;
                res.Code = APIEnums.Success;
                res.Msg = "获取采购订单联查成功";

                if (total == 0)
                {
                    res.PageDatas = new List<PurchaseOrderWithMaterialDto>();
                    return Task.FromResult(res);
                }

                // 2) 分页
                var offset = (request.PageIndex - 1) * request.PageSize;
                if (offset < 0) offset = 0;
                param.Add("Offset", offset);
                param.Add("PageSize", request.PageSize);

                var dataSql = @"SELECT 
                                    p.Id AS PurchaseOrderId,
                                    p.DocCode,
                                    p.supplierId,
                                    p.docDate,
                                    p.docStatus,
                                    COALESCE(s.Supplier_Name, p.supplierId) AS SupplierName,
                                    m.Id AS PurchasingMaterialId,
                                    m.DocumentaryNumber,
                                    m.PlannedOrderNumber,
                                    m.materialCode,
                                    m.materialName,
                                    m.modelSpec,
                                    m.mainUnit,
                                    m.demandDate,
                                    m.qty,
                                    m.arrivalQty,
                                    m.unitPrice,
                                    m.amount
                                " + fromSql + whereSql + @" 
                                ORDER BY p.Id DESC, m.Id DESC
                                LIMIT @Offset, @PageSize";

                var rows = _repo.Query<PurchaseOrderWithMaterialDto>(dataSql, param).ToList();
                res.PageDatas = rows;
            }
            catch (Exception ex)
            {
                res.Code = APIEnums.Error;
                res.Msg = "获取采购订单联查失败";
                _logger.LogError(ex, "获取采购订单联查失败: {Message}", ex.Message);
            }

            return Task.FromResult(res);
        }
    }
}
